package io.xmu.dataanalysis.repository;

import io.xmu.dataanalysis.entity.Shop;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import io.xmu.dataanalysis.entity.ShopDailyData;

import java.math.BigDecimal;
import java.util.List;

public interface ShopDailyDataRepo extends PagingAndSortingRepository<ShopDailyData, Integer> {


    @Modifying
    @Query("delete from ShopDailyData where partition = ?1")
    void deleteByPartition(String partition);

    @Query(value = "SELECT * from shopdailydata where shopid = ?1 AND `partition` = ?2", nativeQuery = true)
    ShopDailyData findByShopIdAndPartition(Integer shopId, String partition);

    @Query(value = "SELECT * FROM ShopDailyData WHERE `shopId` = ?1 AND `partition` > ?2 AND `partition` <= ?3 ORDER BY `partition` DESC ", nativeQuery = true)
    List<ShopDailyData> findByShopIdAndPartitionBetween(Integer shopId, String start, String end);

    @Query(value = "SELECT * FROM shopdailydata WHERE `Partition` = ?1 " +
            " ORDER BY Sales DESC LIMIT 10 ", nativeQuery = true)
    List<ShopDailyData> findSalesTop10ByPartition(String partition);

    @Query(value = " SELECT * FROM shopdailydata WHERE `Partition` = ?1 " +
            " ORDER BY `PV` DESC LIMIT 10", nativeQuery = true)
    List<ShopDailyData> findPVTop10ByPartition(String partition);

    @Query(value = " SELECT * FROM shopdailydata  WHERE ShopId =?1 ORDER BY `Partition` ASC LIMIT 1 ", nativeQuery = true)
    ShopDailyData findTheFirstOneByShopId(Integer shopId);

    @Query(value = " SELECT * FROM shopdailydata  WHERE ShopId =?1 ORDER BY `Partition` DESC LIMIT 1 ", nativeQuery = true)
    ShopDailyData findTheLastOneByShopId(Integer shopId);

    @Query(value = "SELECT * FROM shopdailydata WHERE ShopId =?1 ORDER BY `Partition` ASC", nativeQuery = true)
    List<ShopDailyData> findByShopIdOrderByPartition(Integer shopId);

    @Query(value = "SELECT tmp.rowno FROM " +
            "  (SELECT @rowno\\:=@rowno+1 as rowno , a.Sales AS Sales, a.ShopId as ShopId FROM shopdailydata as a,(SELECT @rowno\\:=0) as b  " +
            "  WHERE a.`Partition`= ?1 " +
            "  AND a.CateId = ?2  " +
            "  ORDER BY Sales DESC)  " +
            "  as tmp " +
            "  WHERE tmp.shopId= ?3 ", nativeQuery = true)
    Integer findSalesRankByPartitionAndCateIdAndShopId(String partition, Integer cateId, Integer shopId);

    @Query(value = "SELECT tmp.rowno FROM  " +
            "  (SELECT @rowno\\:=@rowno+1 as rowno , a.PayedGoodsCount AS PayedGoodsCount, a.ShopId as ShopId FROM shopdailydata as a,(SELECT @rowno\\:=0) as b  " +
            "  WHERE a.`Partition`= ?1 " +
            "  AND a.CateId = ?2  " +
            "  ORDER BY PayedGoodsCount DESC)  " +
            "  as tmp " +
            "  WHERE tmp.shopId= ?3 ", nativeQuery = true)
    Integer findPayedGoodsCountRank(String partition, Integer cateId, Integer shopId);

    @Query(value = "SELECT tmp.rowno FROM  " +
            "  (SELECT @rowno\\:=@rowno+1 as rowno , a.PayedUserCount AS PayedUserCount, a.ShopId as ShopId FROM shopdailydata as a,(SELECT @rowno\\:=0) as b  " +
            "  WHERE a.`Partition`= ?1 " +
            "  AND a.CateId = ?2 " +
            "  ORDER BY PayedUserCount DESC)  " +
            "  as tmp " +
            " WHERE tmp.shopId= ?3", nativeQuery = true)
    Integer findPayedUserCountRank(String partition, Integer cateId, Integer shopId);

    @Query(value = "SELECT tmp.rowno FROM \n" +
            "  (SELECT @rowno\\:=@rowno+1 as rowno, a.PV AS PV, a.ShopId as ShopId FROM shopdailydata as a,(SELECT @rowno\\:=0) as b  " +
            "  WHERE a.`Partition`= ?1 " +
            "  AND a.CateId = ?2  " +
            "  ORDER BY PV DESC)  " +
            "  as tmp " +
            "  WHERE tmp.shopId= ?3", nativeQuery = true)
    Integer findPVRank(String partition, Integer cateId, Integer shopId);

    @Query(value = "SELECT tmp.rowno FROM  " +
            "  (SELECT @rowno\\:=@rowno+1 as rowno , a.UV AS UV, a.ShopId as ShopId FROM shopdailydata as a,(SELECT @rowno\\:=0) as b \n" +
            "  WHERE a.`Partition`= ?1 " +
            "  AND a.CateId = ?2  " +
            "  ORDER BY UV DESC)  " +
            "  as tmp " +
            "WHERE tmp.shopId= ?3", nativeQuery = true)
    Integer findUVRank(String partition, Integer cateId, Integer shopId);

    @Query(value = "SELECT AVG(Sales) FROM shopdailydata WHERE CateId =  " +
            " ( SELECT CateId FROM shop WHERE Id = ?1)  GROUP BY `Partition` ASC", nativeQuery = true)
    List<BigDecimal> findAverageSales(Integer shopId);

    @Query(value = "SELECT AVG(PayedGoodsCount) FROM shopdailydata WHERE CateId = \n" +
            " ( SELECT CateId FROM shop WHERE Id = ?1)  GROUP BY `Partition` ASC", nativeQuery = true)
    List<BigDecimal> findAveragePayedGoodsCount(Integer shopId);

    @Query(value = "SELECT AVG(PayedUserCount) FROM shopdailydata WHERE CateId = \n" +
            "( SELECT CateId FROM shop WHERE Id = ?1) GROUP BY `Partition` ASC", nativeQuery = true)
    List<BigDecimal> findAveragePayedUserCount(Integer shopId);

    @Query(value = "SELECT AVG(PV) FROM shopdailydata WHERE CateId = \n" +
            "( SELECT CateId FROM shop WHERE Id = ?1)  GROUP BY `Partition` ASC", nativeQuery = true)
    List<BigDecimal> findAveragePV(Integer shopId);

    @Query(value = "SELECT AVG(UV) FROM shopdailydata WHERE CateId = \n" +
            "( SELECT CateId FROM shop WHERE Id = ?1)  GROUP BY `Partition` ASC", nativeQuery = true)
    List<BigDecimal> findAverageUV(Integer shopId);

    @Query(value = "SELECT Sales FROM shopdailydata WHERE CateId= " +
            "(SELECT CateId FROM shop WHERE Id = ?1) " +
            "ORDER BY `Partition` ASC,Sales DESC", nativeQuery = true)
    List<Integer> findASales(Integer shopId);

    @Query(value = "SELECT  PayedUserCount FROM shopdailydata WHERE CateId= " +
            "(SELECT CateId FROM shop WHERE Id = ?1) " +
            "ORDER BY `Partition` ASC,  PayedUserCount DESC", nativeQuery = true)
    List<Integer> findAPayedUserCount(Integer shopId);

    @Query(value = "SELECT  PayedGoodsCount FROM shopdailydata WHERE CateId= " +
            "(SELECT CateId FROM shop WHERE Id = ?1) " +
            "ORDER BY `Partition` ASC,  PayedGoodsCount DESC", nativeQuery = true)
    List<Integer> findAPayedGoodsCount(Integer shopId);

    @Query(value = "SELECT  PV FROM shopdailydata WHERE CateId= " +
            "(SELECT CateId FROM shop WHERE Id = ?1) " +
            "ORDER BY `Partition` ASC,  PV DESC", nativeQuery = true)
    List<Integer> findAPV(Integer shopId);

    @Query(value = "SELECT  UV FROM shopdailydata WHERE CateId= " +
            "(SELECT CateId FROM shop WHERE Id = ?1) " +
            "ORDER BY `Partition` ASC,  UV DESC", nativeQuery = true)
    List<Integer> findAUV(Integer shopId);


    @Query(value = "SELECT SUM(`FavoriteCount`) FROM `goodsdailydata` " +
            "WHERE `ShopId` = ?1 AND `Partition` = ?2 ",nativeQuery = true)
    Integer findFavoriteCountByShopIdAndPartition(Integer shopId,String partition);

}
